package com.xianzhiOA.controller;

import com.xianzhiOA.bean.XzAttendance;
import com.xianzhiOA.service.XzAttendanceService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import java.io.*;
import java.util.Date;

/**
 * 表格的导入controller
 *
 * @author 郭云春晓
 * @create 2017-04-18 17:49
 **/
@RequestMapping("BookController")
public class BookController {
    @Autowired
    private XzAttendanceService xzAttendanceService;
    @Autowired
    private XzAttendance xzAttendance;
    //表格所在路径    private String excelPath;
    private FileInputStream fis;

    public boolean importDB(){
        boolean flag = true;
        HSSFWorkbook workbook = new HSSFWorkbook();//创建工作簿
        //打卡机导出的有效sheet从4开始
        int sheetNo = 4;
        HSSFSheet sheet = workbook.getSheetAt(sheetNo);//得到工作表
        HSSFRow row = null;//excel对应的行
        HSSFCell cell = null;//Excel对应的单元格
        HSSFCell cell2 = null;//备用的单元格
        int totalRow = sheet.getLastRowNum();//得到Excel的总记录条数
        int totalCell = row.getLastCellNum();//得到一行中有效的单元格数
        System.out.println(totalRow);
        //以下对应数据库中的字段
        int empNo;//工号
        Date firstTime;//上班时间
        Date lastTime;//下班时间
        for (int i = 9; i <totalCell ; i++) {
            //获取工号,表格中的第4行为工号固定位置
            row = sheet.getRow(3);
            if (i==9||i==24||i==39){
                cell = row.getCell(i);
                empNo = (int) cell.getNumericCellValue();
                //日期循环 第12行开始有效记录考勤时间
                for (int j = 11; j < totalRow; j++) {
                    row = sheet.getRow(j);
                    int first;//上班单元格
                    int last;//下班单元格
                    if (i==9){
                        first=1;
                        last=8;
                        cell = row.getCell(first);
                        cell2 = row.getCell(last);
                    }else if (i==24){
                        first=16;
                        last=23;
                        cell = row.getCell(first);
                        cell2 = row.getCell(last);
                    }else if (i==39){
                        first=31;
                        last=38;
                        cell = row.getCell(first);
                        cell2 = row.getCell(last);
                    }
                    //获取上班时间
                    firstTime = cell.getDateCellValue();
                    //获取下班时间
                    lastTime = cell2.getDateCellValue();
                    //封装成对象
//                  xzAttendance.setEmpNo(empNo);
                    xzAttendance.setFirstTime(firstTime);
                    xzAttendance.setLastTime(lastTime);
                    //数据插入
                    xzAttendanceService.insert(xzAttendance);
                }
            }
        }
        return flag;
    }

}
